package com.cpit.platform.common.excel;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.text.DecimalFormat;
import java.util.*;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.multipart.MultipartFile;

import com.cpit.platform.common.excel.annotation.ExcelField;
import com.cpit.platform.common.utils.Reflections;
import com.google.common.collect.Lists;

/**
 * 导入Excel文件（支持“XLS”和“XLSX”格式）
 * 
 * @author ThinkGem
 * @version 2013-03-10
 */
public class ImportExcel {

	private static Logger log = LoggerFactory.getLogger(ImportExcel.class);

	/**
	 * 工作薄对象
	 */
	private Workbook wb;

	/**
	 * 工作表对象
	 */
	private Sheet sheet;

	/**
	 * 标题行号
	 */
	private int headerNum;
	// 枚举值映射
	Map<ExcelField, Map<String, Integer>> enumMap = new HashMap<ExcelField, Map<String, Integer>>();

	// Long类型枚举值映射，分开处理
	Map<ExcelField, Map<String, Long>> enumMapOfLong = new HashMap<ExcelField, Map<String, Long>>();

	// 转换器映射
	Map<ExcelField, Convertor> convertorMap = new HashMap<ExcelField, Convertor>();

	/**
	 * 构造函数
	 * 
	 * @param path
	 *            导入文件，读取第一个工作表
	 * @param headerNum
	 *            标题行号，数据行号=标题行号+1
	 * @throws InvalidFormatException
	 * @throws IOException
	 */
	public ImportExcel(String fileName, int headerNum) throws InvalidFormatException, IOException {
		this(new File(fileName), headerNum);
	}

	/**
	 * 构造函数
	 * 
	 * @param path
	 *            导入文件对象，读取第一个工作表
	 * @param headerNum
	 *            标题行号，数据行号=标题行号+1
	 * @throws InvalidFormatException
	 * @throws IOException
	 */
	public ImportExcel(File file, int headerNum) throws InvalidFormatException, IOException {
		this(file, headerNum, 0);
	}

	/**
	 * 构造函数
	 * 
	 * @param path
	 *            导入文件
	 * @param headerNum
	 *            标题行号，数据行号=标题行号+1
	 * @param sheetIndex
	 *            工作表编号
	 * @throws InvalidFormatException
	 * @throws IOException
	 */
	public ImportExcel(String fileName, int headerNum, int sheetIndex) throws InvalidFormatException, IOException {
		this(new File(fileName), headerNum, sheetIndex);
	}

	/**
	 * 构造函数
	 * 
	 * @param path
	 *            导入文件对象
	 * @param headerNum
	 *            标题行号，数据行号=标题行号+1
	 * @param sheetIndex
	 *            工作表编号
	 * @throws InvalidFormatException
	 * @throws IOException
	 */
	public ImportExcel(File file, int headerNum, int sheetIndex) throws InvalidFormatException, IOException {
		this(file.getName(), new FileInputStream(file), headerNum, sheetIndex);
	}

	/**
	 * 构造函数
	 * 
	 * @param file
	 *            导入文件对象
	 * @param headerNum
	 *            标题行号，数据行号=标题行号+1
	 * @param sheetIndex
	 *            工作表编号
	 * @throws InvalidFormatException
	 * @throws IOException
	 */
	public ImportExcel(MultipartFile multipartFile, int headerNum, int sheetIndex)
			throws InvalidFormatException, IOException {
		this(multipartFile.getOriginalFilename(), multipartFile.getInputStream(), headerNum, sheetIndex);
	}

	/**
	 * 构造函数
	 * 
	 * @param path
	 *            导入文件对象
	 * @param headerNum
	 *            标题行号，数据行号=标题行号+1
	 * @param sheetIndex
	 *            工作表编号
	 * @throws InvalidFormatException
	 * @throws IOException
	 */
	public ImportExcel(String fileName, InputStream is, int headerNum, int sheetIndex)
			throws InvalidFormatException, IOException {
		if (StringUtils.isBlank(fileName)) {
			throw new RuntimeException("导入文档为空!");
		} else if (fileName.toLowerCase().endsWith("xls")) {
			this.wb = new HSSFWorkbook(is);
		} else if (fileName.toLowerCase().endsWith("xlsx")) {
			this.wb = new XSSFWorkbook(is);
		} else {
			throw new RuntimeException("文档格式不正确!");
		}
		if (this.wb.getNumberOfSheets() < sheetIndex) {
			throw new RuntimeException("文档中没有工作表!");
		}
		this.sheet = this.wb.getSheetAt(sheetIndex);
		this.headerNum = headerNum;
		log.debug("Initialize success.");
	}

	public Workbook getWb() {
		return wb;
	}

	public void setWb(Workbook wb) {
		this.wb = wb;
	}

	public Sheet getSheet() {
		return sheet;
	}

	public void setSheet(Sheet sheet) {
		this.sheet = sheet;
	}

	public int getHeaderNum() {
		return headerNum;
	}

	public void setHeaderNum(int headerNum) {
		this.headerNum = headerNum;
	}

	/**
	 * 获取行对象
	 * 
	 * @param rownum
	 * @return
	 */
	public Row getRow(int rownum) {
		return this.sheet.getRow(rownum);
	}

	/**
	 * 获取数据行号
	 * 
	 * @return
	 */
	public int getDataRowNum() {
		return headerNum + 1;
	}

	/**
	 * 获取最后一个数据行号
	 * 
	 * @return
	 */
	public int getLastDataRowNum() {
		return this.sheet.getLastRowNum() + headerNum + 1;
	}

	/**
	 * 获取最后一个列号
	 * 
	 * @return
	 */
	public int getLastCellNum() {
		return this.getRow(headerNum).getLastCellNum();
	}

	/**
	 * 校验导入文件的表头是否正确
	 * 
	 * @return
	 */
	public String validateHead(String[] title) {

		StringBuffer sb = new StringBuffer();
		Row row = getRow(headerNum);
		// 导入文件中只要前面的列符合就OK，多余的可以不管
		if (row == null || row.getLastCellNum() < title.length - 1) {
			sb.append("标题行有误，请核查，正确的应该为：" + Arrays.toString(title));
			return sb.toString();
		}
		Cell cell = null;

		List<String> tilteList = new ArrayList<String>();
		// 迭代列数据,只取前15列作为必选数据，
		for (int j = 0; j < title.length; j++) {
			cell = row.getCell(j);
			if (cell == null) {
				continue;
			}

			switch (cell.getCellType()) {
			case XSSFCell.CELL_TYPE_STRING:
				// System.out.println(i + "行" + j + " 列 is String type");
				tilteList.add(cell.getStringCellValue());
				break;
			default:
				continue;
			}
		}

		if (tilteList.size() != title.length) {
			sb.append("标题行有误，请核查，正确的应该为：" + Arrays.toString(title));
			return sb.toString();
		}

		for (int i = 0; i < tilteList.size(); i++) {
			if (!((String) tilteList.get(i)).equals(title[i])) {
				sb.append("标题行有误，请核查，正确的应该为：" + Arrays.toString(title));
				return sb.toString();
			}
		}

		return sb.toString();
	}

	/**
	 * 获取单元格值
	 * 
	 * @param row
	 *            获取的行
	 * @param column
	 *            获取单元格列号
	 * @return 单元格值
	 */
	public Object getCellValue(Row row, int column) {

		// DecimalFormat df = new DecimalFormat("0.0000000");// 格式化 number
		// String字符
		DecimalFormat nf = new DecimalFormat("0");// 格式化数字
		// SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");//
		// 格式化日期字符串
		Object val = "";
		try {
			Cell cell = row.getCell(column);
			if (cell != null) {
				if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {

					if ("@".equals(cell.getCellStyle().getDataFormatString())) {
						val = nf.format(cell.getNumericCellValue());
					} else if ("0".equals(cell.getCellStyle().getDataFormatString())) {
						val = nf.format(cell.getNumericCellValue());
					} else {
						val = cell.getNumericCellValue();
					}

					// val = cell.getNumericCellValue();
				} else if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
					val = cell.getStringCellValue();
				} else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
					val = cell.getCellFormula();
				} else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
					val = cell.getBooleanCellValue();
				} else if (cell.getCellType() == Cell.CELL_TYPE_ERROR) {
					val = cell.getErrorCellValue();
				}
			}
		} catch (Exception e) {
			return val;
		}
		return val;
	}

	private <E> void makeAnnotationList(Class<E> cls, List<Object[]> annotationList, int... groups) {
		// Get annotation field
		Field[] fs = cls.getDeclaredFields();
		for (Field f : fs) {
			ExcelField ef = f.getAnnotation(ExcelField.class);
			if (ef != null && (ef.type() == 0 || ef.type() == 2)) {
				if (groups != null && groups.length > 0) {
					boolean inGroup = false;
					for (int g : groups) {
						if (inGroup) {
							break;
						}
						for (int efg : ef.groups()) {
							if (g == efg) {
								inGroup = true;
								annotationList.add(new Object[] { ef, f });
								break;
							}
						}
					}
				} else {
					annotationList.add(new Object[] { ef, f });
				}
				parseEnumDefinition(ef);
				parseConvertorDefinition(ef);
			}
		}
		// Get annotation method
		Method[] ms = cls.getDeclaredMethods();
		for (Method m : ms) {
			ExcelField ef = m.getAnnotation(ExcelField.class);
			if (ef != null && (ef.type() == 0 || ef.type() == 2)) {
				if (groups != null && groups.length > 0) {
					boolean inGroup = false;
					for (int g : groups) {
						if (inGroup) {
							break;
						}
						for (int efg : ef.groups()) {
							if (g == efg) {
								inGroup = true;
								annotationList.add(new Object[] { ef, m });
								break;
							}
						}
					}
				} else {
					annotationList.add(new Object[] { ef, m });
				}
				parseEnumDefinition(ef);
				parseConvertorDefinition(ef);
			}
		}
	}

	private void parseConvertorDefinition(ExcelField ef) {
		String strConvertor = ef.convertor();
		if (!strConvertor.isEmpty()) {
			Convertor convertor = null;
			try {
				convertor = (Convertor) Class.forName(strConvertor).newInstance();
				convertorMap.put(ef, convertor);
			} catch (Exception ex) {
				ex.printStackTrace();
			}
		}
	}

	private void parseEnumDefinition(ExcelField ef) {
		String enums = ef.enums();
		if (!enums.isEmpty()) {
			// 去掉首尾的{}，并按,分解
			String[] items = enums.substring(1, enums.length() - 1).split(",");

			Map<String, Integer> map = new HashMap<String, Integer>(items.length);

			// Long类型枚举值映射，分开处理
			Map<String, Long> mapOfLong = new HashMap<String, Long>(items.length);

			for (String item : items) {
				int idx = item.indexOf("=");
				String key = item.substring(0, idx).trim();
				String value;
				// 去掉首尾的"
				if (item.indexOf("I18n:", idx + 1) == (idx + 1)) {
					// I18n
					value = item.substring(idx + 2 + "I18n:".length(), item.length() - 1).trim();
					// value = I18n.getMsg(key);
				} else {
					value = item.substring(idx + 2, item.length() - 1).trim();
				}

				try {
					mapOfLong.put(value, Long.valueOf(key));
					map.put(value, Integer.valueOf(key));
				} catch (Exception ex) {
					continue;
				}
			}

			enumMapOfLong.put(ef, mapOfLong);
			enumMap.put(ef, map);
		}
	}

	private <E> List<E> getDataList(Class<E> cls, List<Object[]> annotationList) throws Exception {
		// Get excel data
		List<E> dataList = Lists.newArrayList();
		String err = "";
		for (int i = this.getDataRowNum(), total = this.getLastDataRowNum(); i < total; i++) {
			E e = (E) cls.newInstance();
			int column = 0;
			Row row = this.getRow(i);
			StringBuilder sb = new StringBuilder();
			for (Object[] os : annotationList) {
				Object val = this.getCellValue(row, column++);
				if (val != null) {
					ExcelField ef = (ExcelField) os[0];

					Convertor convertor = convertorMap.get(ef);
					if (convertor != null) {
						val = convertor.convert4Import(val, row);
					}

					// Get param type and type cast
					Class<?> valType = Class.class;
					String name = ef.title();
					if (os[1] instanceof Field) {
						valType = ((Field) os[1]).getType();
					} else if (os[1] instanceof Method) {
						Method method = ((Method) os[1]);
						if ("get".equals(method.getName().substring(0, 3))) {
							valType = method.getReturnType();
						} else if ("set".equals(method.getName().substring(0, 3))) {
							valType = ((Method) os[1]).getParameterTypes()[0];
						}
					}
					// log.debug("Import value type: ["+i+","+column+"] " +
					// valType);
					if (valType == String.class) {
						String s = String.valueOf(val);
						//val.toString().trim());
						if (val instanceof Double || val instanceof Integer || val instanceof Float && s.indexOf("E") > 0) {
							DecimalFormat format = new DecimalFormat("#");
							s =  format.format(val);
						}

						if (s.contains("\\.")) {
							String[] subS = s.split("\\.");
							if (subS.length == 4) {
								// ip地址
								val = String.valueOf(val.toString().trim());
							} else {
								if (StringUtils.endsWith(s, ".0")) {
									val = StringUtils.substringBefore(s, ".0");
								} else {
									val = s;//String.valueOf(val);//val.toString().trim());
								}
							}
						} else if (s.equals("'")) {
							val = "";
						} else if (s.startsWith("'")){
							val =s.substring(1, s.length()-1);
						} else {
							val = s;//String.valueOf(val.toString().trim());
						}
					} else if ("".equals(val)) {
						val = null;
					} else {
						if (valType == Integer.class || valType == int.class) {
							String strVal = val.toString().trim();
							if (ef.enums() != null && !ef.enums().isEmpty()) {
								Map<String, Integer> enums = enumMap.get(ef);
								Integer v = enums.get(strVal);

								if (v == null) {
									if (strVal.equals("true")) {
										strVal = "TRUE";
									} else if (strVal.equals("false")) {
										strVal = "FALSE";
									}
									v = enums.get(strVal);
									if (v == null) {
										err = err + (i + 1) + "行" + name + "的枚举值错误；";
									} else {
										val = v;
									}
								} else {
									val = v;
								}
							} else {
								try {
									val = Integer.valueOf(String.valueOf(val));// Double.valueOf(strVal).intValue();
								} catch (Exception ex) {
									err = err + (i + 1) + "行" + name + "的值类型错误，应该是数字类型；";
								}
							}
						} else if (valType == Long.class || valType == long.class) {
							String strVal = val.toString().trim();
							if (ef.enums() != null && !ef.enums().isEmpty()) {
								// 使用Long类型枚举值处理
								Map<String, Long> enums = enumMapOfLong.get(ef);
								Long v = enums.get(strVal);

								if (v == null) {
									if (strVal.equals("true")) {
										strVal = "TRUE";
									} else if (strVal.equals("false")) {
										strVal = "FALSE";
									}
									v = enums.get(strVal);
									if (v == null) {
										err = err + (i + 1) + "行" + name + "的枚举值错误；";
									} else {
										val = v;
									}
								} else {
									val = v;
								}
							} else {
								try {
									val =  Long.valueOf(String.valueOf(val));//Double.valueOf(strVal).longValue();
								} catch (Exception ex) {
									err = err + (i + 1) + "行" + name + "的值类型错误，应该是数字类型；";
								}
							}
						} else if (valType == Double.class) {
							try {
								val = Double.valueOf(String.valueOf(val));//val.toString().trim());
							} catch (Exception ex) {
								err = err + (i + 1) + "行" + name + "的值类型错误，应该是数字类型；";
							}
						} else if (valType == Float.class) {
							try {
								val = Float.valueOf(String.valueOf(val));//val.toString().trim());
							} catch (Exception ex) {
								err = err + (i + 1) + "行" + name + "的值类型错误，应该是数字类型；";
							}
						} else if (valType == Date.class) {
							try {
								if (val instanceof Double)
									val = DateUtil.getJavaDate((Double) val);
								else {
									val = com.cpit.platform.common.utils.DateUtil.parseDate(val);
								}
								if (val == null) {
									err = err + (i + 1) + "行" + name + "的值类型错误，应该是日期类型；";
								}
							} catch (Exception ex) {
								err = err + (i + 1) + "行" + name + "的值类型错误，应该是日期类型；";
							}
						} else if (valType == Boolean.class) {
							String strVal = val.toString().trim();
							if (ef.enums() != null && !ef.enums().isEmpty()) {
								Map<String, Integer> enums = enumMap.get(ef);
								Integer v = enums.get(strVal);
								if (v == null) {
									if (strVal.equals("true")) {
										strVal = "TRUE";
									} else if (strVal.equals("false")) {
										strVal = "FALSE";
									}
									v = enums.get(strVal);
									if (v == null) {
										err = err + (i + 1) + "行" + name + "的枚举值错误；";
									} else {
										val = (v == 1);
									}
								} else {
									val = (v == 1);
								}
							} else {
								try {
									val = Boolean.valueOf(strVal);
								} catch (Exception ex) {
									err = err + (i + 1) + "行" + name + "的值类型错误，应该是布尔类型；";
								}
							}
						} else {
							try {
								if (ef.fieldType() != Class.class) {
									val = ef.fieldType().getMethod("getValue", String.class).invoke(null,
											val.toString().trim());
								} else {
									val = Class
											.forName(this.getClass().getName().replaceAll(
													this.getClass().getSimpleName(),
													"fieldtype." + valType.getSimpleName() + "Type"))
											.getMethod("getValue", String.class).invoke(null, val.toString());
								}
							} catch (Exception ex) {
								err = err + (i + 1) + "行" + name + "的值有误；";
							}
						}
					}
					if (err.length() > 0) {
						// 有属性非法
						continue;
					}
					// set entity value
					try {
						if (os[1] instanceof Field) {
							Reflections.invokeSetter(e, ((Field) os[1]).getName(), val);
						} else if (os[1] instanceof Method) {
							String mthodName = ((Method) os[1]).getName();
							if ("get".equals(mthodName.substring(0, 3))) {
								mthodName = "set" + StringUtils.substringAfter(mthodName, "get");
							}
							Reflections.invokeMethod(e, mthodName, new Class[] { valType }, new Object[] { val });
						}
					} catch (Exception ex) {
						// 导出的文件包含一些get方法，无对应的set方法，忽略即可。
						continue;
					}
				}
				sb.append(val + ", ");
			}
			dataList.add(e);
			log.debug("Read success: [" + i + "] " + sb.toString());
		}
		if (err.length() > 0) {
			throw new Exception(err);
		}
		return dataList;

	}

	/**
	 * 获取导入数据列表
	 *
	 * @param cls
	 *            导入对象类型
	 * @param groups
	 *            导入分组
	 * @throws Exception
	 */
	public <E> List<E> getDataList(Class<E> cls, int... groups) throws Exception {
		List<Object[]> annotationList = Lists.newArrayList();
		makeAnnotationList(cls, annotationList, groups);
		// Field sorting
		Collections.sort(annotationList, new Comparator<Object[]>() {
			public int compare(Object[] o1, Object[] o2) {
				return new Integer(((ExcelField) o1[0]).sort()).compareTo(new Integer(((ExcelField) o2[0]).sort()));
			}
		});
		return getDataList(cls, annotationList);
	}

	/**
	 * 获取导入数据列表
	 *
	 * @param cls
	 *            导入对象类型
	 * @param fieldNames
	 *            排好序的属性名集合，导入时按此顺序将excel中各列对应到对应的对象属性上
	 * @param groups
	 *            导入分组
	 * @throws Exception
	 */
	public <E> List<E> getDataList(Class<E> cls, Collection<String> fieldNames, int... groups) throws Exception {
		List<Object[]> annotationList = Lists.newArrayList();
		makeAnnotationList(cls, annotationList, groups);

		List<Object[]> annotationList1 = Lists.newArrayList();
		for	(String fieldName:fieldNames) {
			for (Object[] os : annotationList) {
				Field field = (Field) os[1];
				if (field.getName().equals(fieldName)) {
					annotationList1.add(os);
					break;
				}
			}
		}
		return getDataList(cls, annotationList1);
	}
	// /**
	// * 导入测试
	// */
	// public static void main(String[] args) throws Throwable {
	//
	// ImportExcel ei = new ImportExcel("target/export.xlsx", 1);
	//
	// for (int i = ei.getDataRowNum(); i < ei.getLastDataRowNum(); i++) {
	// Row row = ei.getRow(i);
	// for (int j = 0; j < ei.getLastCellNum(); j++) {
	// Object val = ei.getCellValue(row, j);
	// System.out.print(val+", ");
	// }
	// System.out.print("\n");
	// }
	//
	// }

}
